﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DataControl;
using System.Data;
using System.Text;
using System.Collections;

namespace ATW.MT.BUS
{
    public class bus_T_DemandInformation
    {
        DataAccess da = null;
        public bus_T_DemandInformation()
        {
            da = new DataAccess();
        }
        #region 方法
        /// <summary>
        /// 需求分页查询
        /// </summary>
        /// <param name="title">标题</param>
        /// <param name="type">类别</param>
        /// <param name="status">状态 0默认（未审核） 1正常（审核通过） 2删除 3审核未通过</param>
        /// <param name="pstart">发布时间开始</param>
        /// <param name="pend">发布时间结束</param>
        /// <param name="fstart">截止时间开始</param>
        /// <param name="fend">截至时间结束</param>
        /// <param name="page"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public DataTable DemandPagerQuery(string title, string name, string type, string status, string pstart, string pend,
            string fstart, string fend, int page, int pagesize)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"select d.TDIID,case d.tdiType when '1' then '物供'
							  when '2' then '质量控制'
							  when '3' then '研发'
							  when '4' then '代理商'
							  when '5' then '人员招聘'
							  when '6' then '设备租赁和承包'
							  when '7' then '合资工厂'
							  when '8' then '全球销售和服务'
							  when '9' then '销售' end tditype,
								d.tdiTitle,d.tdiDesc,d.tdino,
	                            convert(varchar(11),d.tdiPublishTime,111) tdiPublishTime,
	                            convert(varchar(11),d.tdiCloseTime,111) tdiCloseTime,
	                           case d.tdiRowStatus when '0' then '未审核'
												   when '1' then '审核通过'
												   when '2' then '已删除'
                                                   when '3' then '已到期'
                                                   when '4' then '完成' end tdirowstatus,
							isnull(s.num,0) num,u.tauName 
                            from T_DemandInformation d left join 
                            (select COUNT(*) num,tdiNo from T_DemandInformationBid db group by tdiNo)s on s.tdiNo=d.tdiNo 
                            left join T_AppUser u on d.tauLoginId=u.tauLoginId
                            where 1=1");
            if (!String.IsNullOrEmpty(title))
            {
                sql.Append(" and d.tdiTitle like '%" + title + "%'");
            }
            if (!String.IsNullOrEmpty(name))
            {
                sql.Append(" and u.tauName like '%" + name + "%'");
            }
            if (!String.IsNullOrEmpty(type))
            {
                sql.Append(" and d.tdiType='" + type + "'");
            }
            if (!String.IsNullOrEmpty(status))
            {
                sql.Append(" and d.tdirowstatus ='" + status + "'");
            }
            if (!String.IsNullOrEmpty(pstart))
            {
                sql.Append(" and d.tdiPublishTime>cast('" + pstart + " 00:00:00' as datetime)");
            }
            if (!String.IsNullOrEmpty(pend))
            {
                sql.Append(" and d.tdiPublishTime<cast('" + pend + " 23:59:59' as datetime)");
            }
            if (!String.IsNullOrEmpty(fstart))
            {
                sql.Append(" and d.tdiCloseTime>cast('" + fstart + " 00:00:00' as datetime)");
            }
            if (!String.IsNullOrEmpty(fend))
            {
                sql.Append(" and d.tdiCloseTime<cast('" + fend + " 23:59:59' as datetime)");
            }
            return da.PageSize(sql.ToString(), page, pagesize, "tdiid");
        }
        /// <summary>
        /// 需求详情
        /// </summary>
        /// <param name="tdiid"></param>
        /// <returns></returns>
        public DataTable DemandDetail(string tdiid)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"select d.TDIID,info.ticName,case d.tdiType when '1' then '物供'
							  when '2' then '质量控制'
							  when '3' then '研发'
							  when '4' then '代理商'
							  when '5' then '人员招聘'
							  when '6' then '设备租赁和承包'
							  when '7' then '合资工厂'
							  when '8' then '全球销售和服务'
							  when '9' then '销售' end tditype,
								d.tdiTitle,d.tdiDesc,
	                            convert(varchar(11),d.tdiPublishTime,111) tdiPublishTime,
	                            convert(varchar(11),d.tdiCloseTime,111) tdiCloseTime,
	                           case d.tdiRowStatus when '0' then '未审核'
												   when '1' then '审核通过'
												   when '2' then '已删除' end tdirowstatus,
							u.tauName,cast (dd.tdidQty as int) tdiQty, it.diName,it.diUnit,it.diDesc,
                            d.tdiAdditional1,d.tdiAdditional2 
                            from T_DemandInformation d 
                            left join T_AppUser u on d.tauLoginId=u.tauLoginId
                            left join T_DemandInformationDetail dd on dd.tdiNo=d.tdiNo
                            left join D_Item it on it.DIID=dd.DiId
                            left join T_InformationCategory info on info.TICID=d.TicId
                            where 1=1 and d.tdino='" + tdiid + "'");
            return da.ExecuteDataTable(sql.ToString());
        }
        /// <summary>
        /// 删除需求
        /// </summary>
        /// <param name="tdiid"></param>
        /// <returns></returns>
        public int DemandDel(string tdiid)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"update T_DemandInformation set tdirowstatus ='2' where tdiid='" + tdiid + "'");
            return da.ExcuteNoQuery(sql.ToString());
        }
        /// <summary>
        /// 恢复需求
        /// </summary>
        /// <param name="tdiid"></param>
        /// <returns></returns>
        public int DemandRecover(string tdiid)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"update T_DemandInformation set tdirowstatus='0' where tdiid='" + tdiid + "'");
            return da.ExcuteNoQuery(sql.ToString());
        }
        /// <summary>
        /// 需求审核
        /// </summary>
        /// <param name="tdiid"></param>
        /// <param name="flag"></param>
        /// <returns></returns>
        public int DemandAudit(string tdiid, string flag)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"update T_DemandInformation set tdirowstatus ='1' where tdiid='" + tdiid + "'");
            return da.ExcuteNoQuery(sql.ToString());
        }
        /// <summary>
        /// 竞价查询
        /// </summary>
        /// <param name="demandid"></param>
        /// <returns></returns>
        public DataTable BidQuery(string demandid)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"select bid.TDIBID,
	                                   d.tdiTitle,
	                                   d.TDIID,
                                       bid.tdibno,
                                       d.tdino,
	                                   u.tauName,
	                                   cast( isnull(bid.tdibTotalPrice/c.rate,'0') as decimal(15,2)) totalPrice,
	                                   convert(nvarchar(20),bid.CreatedDate,111) [date],
	                                   tdibDeliveryPeriod,
	                                   tdibDeliveryPeriodUnit,
	                                   case bid.tdibRowSatus when '0' then '正常'
															 when '1' then '中标'
															 when '2' then '删除'
															 when '3' then '未中标'
															 when '4' then '流标' end rowstatus
	                                    from T_DemandInformationBid bid 
                                left join T_DemandInformation d on bid.tdiNo=d.tdiNo
                                left join T_AppUser u on u.tauLoginId=bid.tauLoginId
                                left join D_Currency c on bid.tdibCurrency=c.dcName where 1=1 and bid.tdino='{0}'", demandid);
            return da.ExecuteDataTable(sql.ToString());
        }
        /// <summary>
        /// 竞价明细查询
        /// </summary>
        /// <param name="bid"></param>
        /// <returns></returns>
        public DataTable BidDetailQuery(string bid)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"select bd.TDIBDID bdid,
                         i.diName name,
                         cast(d.tdidQty as decimal(15,2)) qty,
                         i.diUnit unit,
                         i.[Level],
                         cast(isnull(bd.tdibdPrice,0)/c.rate as decimal(15,2)) price,
                         d.tdiNo,
                         b.tdibpaytype,
                         CAST(isnull(bd.tdibdPrice,0)*cast(d.tdidQty as decimal(15,2))/c.rate as decimal(15,2)) total
                          from T_DemandInformationBidDetail bd 
                left join T_DemandInformationDetail d on bd.TdidId=d.TDIDID
                left join T_DemandInformationBid b on bd.tdibNo=b.tdibNo
                left join D_Currency c on c.dcName=b.tdibCurrency
                left join D_Item i on i.DIID=d.DiId where 1=1 and d.tdiNo='" + bid + "'");
            return da.ExecuteDataTable(sql.ToString());
        }
        /// <summary>
        /// 中标
        /// </summary>
        /// <param name="tdiid">需求表头</param>
        /// <param name="did">中标id</param>
        /// <returns></returns>
        public bool DemandClose(string tdiid, string did)
        {
            ArrayList al = new ArrayList();
            al.Add("update T_DemandInformationBid set tdibRowSatus='3' where tdibRowSatus<>'4' and Tdino='" + tdiid + "'");
            al.Add("update T_DemandInformationBid set tdibRowSatus='1' where TDIBno='" + did + "'");
            return da.ExecuteArrayList(al);
        }
        /// <summary>
        /// 查询截至日期
        /// </summary>
        /// <param name="tdino"></param>
        /// <returns></returns>
        public string GetDemandCloseDate(string tdino)
        {
            string sql = "select CONVERT(nvarchar(10),tdiCloseTime,111) closeTime from T_DemandInformation where tdiNo='" + tdino + "'";
            object o = da.ExecuteScalar(sql);
            if (o != null && o.ToString() != "")
            {
                return o.ToString();
            }
            return "";
        }
        /// <summary>
        /// 获取投标状态
        /// </summary>
        /// <param name="tdibino"></param>
        /// <returns>0正常（待选） 1中标 2删除 3 未中 4流标</returns>
        public string GetStatus(string tdibino)
        {
            string sql = "select tdibRowSatus from T_DemandInformationBid where tdibNo='" + tdibino + "'";
            object o = da.ExecuteScalar(sql);
            if (o != null && o.ToString() != "")
            {
                return o.ToString();
            }
            return "";
        }
        #endregion
    }
}